using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
namespace SharpSoft.Data
{
    using GSQL;
    using System.Buffers;
    using System.Runtime.CompilerServices;

    /*
     *https://www.baidu.com/link?url=C9oIc1ym2a5d6JaPpqgn_iBdbxtGeG_HFHAFjIfqHvGVBQsaNGO2SahNaGRftjcF54Fuj_vfL2RXuksF2ON_na&wd=&eqid=966a90e30002404a000000065ef8c36e
     NET Core的数据库连接与.NET Framework略有不同。在.NET Framework中，程序可用的数据库驱动程序在整个系统范围内（通过machine.config）自动可用。也就是 mysql的.net连接驱动（mysql-connector-net-x.x.x.x.msi）就可以了。
而.NET Core中不是这种情况，您必须注册要使用的数据库驱动程序-通常在Program.cs或中Startup.cs。使用 以下DbProviderFactories.RegisterFactory方法完成注册：

SQL服务器

using System.Data.SqlClient;
DbProviderFactories.RegisterFactory("System.Data.SqlClient", SqlClientFactory.Instance);
MySQL的

using MySql.Data.MySqlClient;
DbProviderFactories.RegisterFactory("MySql.Data.MySqlClient", MySqlClientFactory.Instance);
PostgreSQL的

using Npgsql;
DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance);
SQLite的

using Microsoft.Data.Sqlite;
DbProviderFactories.RegisterFactory("Microsoft.Data.Sqlite", SqliteFactory.Instance);
     */

    /// <summary>
    /// 用于管理数据库连接，以及提供一套同一的数据库操作方法
    /// </summary>
    public abstract class Database : IDisposable
    {
        #region Base
        protected TransactionWarpper transaction = null;//当前正在执行的事务
        protected readonly DbConnection conn = null;
        protected DbProviderFactory factory = null;
        private string connstr;
        /// <summary>
        /// 指令超时时间(单位：秒)
        /// </summary>
        public int CommandTimeout { get; set; } = 30;
        protected Database(DbProviderFactory p_factory, string p_connstr) : this(p_factory)
        {
            conn.ConnectionString = connstr = p_connstr;
        }
        protected Database(DbProviderFactory p_factory)
        {

#if DEBUG
            info("创建数据连接实例.");
#endif
            factory = p_factory;
            conn = factory.CreateConnection();
            conn.Disposed += Conn_Disposed;
            sqlTextGenerator = SQLTextGenerator();
            if (sqlTextGenerator == null)
            {
                throw new Exception("当前数据库对象未指定SQLTextGenerator。");
            }
        }
        /// <summary>
        /// 测试连接
        /// </summary>
        [System.Diagnostics.DebuggerStepThrough]
        public void TestConnect()
        {
            try
            {
                conn.Open();
            }
            catch (Exception)
            {
                throw;
            }
            if (conn.State != ConnectionState.Open)
            {
                throw new Exception("数据库连接未成功。");
            }
            try
            {
                conn.Close();
            }
            catch (Exception)
            {
                throw;
            }

        }

        private static Type[] _SupportedDbTypes = new Type[] { typeof(MySql), typeof(MsSql), typeof(Sqlite) };
        /// <summary>
        /// 获取支持的数据库类型
        /// </summary>
        public static Type[] SupportedDbTypes
        {
            get
            {
                return _SupportedDbTypes;
            }
        }
        /// <summary>
        /// 获取数据库类型的标注名称
        /// </summary>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static string GetDisplayName(Type dbtype)
        {
            if (dbtype == null)
            {
                return string.Empty;
            }
            var disattr = dbtype.GetCustomAttribute<System.ComponentModel.DisplayNameAttribute>();
            if (disattr != null)
            {
                return disattr.DisplayName;
            }
            return string.Empty;
        }
        private void Conn_Disposed(object sender, EventArgs e)
        {
            IsDisposed = true;
        }

        protected readonly SQLTextGenerator sqlTextGenerator;
        /// <summary>
        /// 在派生类中实现为当前数据库生成SQL脚本
        /// </summary>
        /// <returns></returns>
        public abstract SQLTextGenerator SQLTextGenerator();
        /// <summary>
        /// 根据指定的数据库类型名称和连接字符串创建数据库实例。
        /// </summary>
        /// <param name="dbtype"></param>
        /// <param name="connstr"></param>
        /// <returns></returns>
        public static Database CreateInstance(string dbtype, string connstr)
        {
            var t = Type.GetType(dbtype);
            if (t == null)
            {
                throw new NotSupportedException($"database type \"{dbtype}\" not founded.please check the typename.");
            }

            return CreateInstance(t, connstr);
        }
        /// <summary>
        /// 创建指定数据库类型的实例
        /// </summary>
        /// <param name="t"></param>
        /// <param name="connstr"></param>
        /// <returns></returns>
        public static Database CreateInstance(Type t, string connstr)
        {
            if (t == null)
            {
                throw new ArgumentNullException("t", "database type can not be null.");
            }
            var ci = t.GetConstructor(new Type[] { typeof(string) });
            var obj = ci.Invoke(new object[] { connstr });
            return (Database)obj;
        }

        /// <summary>
        /// 获取或设置当前数据库的连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return conn.ConnectionString; }
            set { conn.ConnectionString = this.connstr = value; }
        }
        /// <summary>
        /// 获取当前数据连接
        /// </summary>
        public DbConnection Connection => conn;

        /// <summary>
        /// 获取数据连接的超时时间
        /// </summary>
        public int ConnectionTimeout { get { return conn.ConnectionTimeout; } }
        /// <summary>
        /// 是否在任意指令执行完毕后立即关闭数据连接
        /// </summary>
        public bool CloseWhenExecuted { get; set; } = false;
        /// <summary>
        /// 是否在释放数据库连接时清除连接池信息
        /// </summary>
        public bool ClearPoolWhenDispose { get; set; } = true;
        /// <summary>
        /// 获取数据库工厂实例
        /// </summary>
        public virtual DbProviderFactory Factory
        {
            get { return factory; }
        }
        /// <summary>
        /// 打开数据连接（如果当前数据连接已关闭）
        /// </summary>
        public void Open()
        {
            if (conn.State == ConnectionState.Closed)
            {
#if DEBUG
                info($"尝试打开数据连接...");
#endif
                conn.Open();
            }
        }
        /// <summary>
        /// 关闭数据连接（如果当前数据连接不是关闭状态）
        /// </summary>
        public void Close()
        {
            try
            {
                //if (conn.State != ConnectionState.Closed)
                //{
#if DEBUG
                info($"关闭数据连接...");
#endif
                conn.Close();
                //}
            }
            catch (Exception ex)
            {
                throw new Exception("关闭数据连接失败", ex);
            }


        }
        private DbCommand CreateCommand()
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = this.CommandTimeout;
            if (transaction != null)
            {
                cmd.Transaction = transaction.Transaction;
            }
            return cmd;
        }
        protected DbCommand CreateCommand(string sql)
        {
            DbCommand cmd = CreateCommand();
            cmd.CommandText = sql;
            return cmd;
        }
        /// <summary>
        /// 处理sql指令的传入参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        protected virtual DbParameter processParameter(DbCommand cmd, string name, object value)
        {
            if (!name.StartsWith("@"))
            {
                name = "@" + name;
            }

            DbParameter para = cmd.CreateParameter();
            para.ParameterName = name;
            if (value is ICollection inp)
            {
                return null;
                throw new Exception("还未找到参数输入迭代数据的方法。20190405   https://stackoverflow.com/questions/1968763/pass-an-array-as-value-in-an-ado-net-dbparameter");
                /*    List<string> l = new List<string>();
                    foreach (var item in inp)
                    {
                        switch (item)
                        {
                            case SByte sbyt:
                            case Byte byt:
                            case ushort usho:
                            case short sho:
                            case int i:
                            case uint ui:
                            case long lon:
                            case ulong ulon:
                            case float f:
                            case double dou:
                            case decimal dec:
                                l.Add(item.ToString());
                                break;
                            case bool boo:
                                l.Add(new System.Data.SqlTypes.SqlBoolean(boo).ToString());
                                break;
                            case DateTime dt:
                                l.Add("'" + new System.Data.SqlTypes.SqlDateTime(dt).ToString() + "'");
                                break;
                            case string str:
                            default:
                                if (item == null)
                                {
                                    l.Add(new System.Data.SqlTypes.SqlString(null).ToString());
                                }
                                l.Add("'" + new System.Data.SqlTypes.SqlString(item.ToString()).ToString().Replace("'", "''") + "'");
                                break;
                        }
                    }
                    para.DbType = DbType.Object;
                    para.Value = $"{string.Join(",", l.ToArray())}";
                    */
            }
            else
            {
                if (value == null)
                {
                    value = DBNull.Value;//20180102新增，将null转化为DBNull参数，为解决sqlite中为可空字段传入null值时的“value must be set”异常。
                    para.IsNullable = true;
                }
                else if (value is string)
                {
                    para.DbType = DbType.String;
                }
                else if (value is DateTime)
                {
                    para.DbType = DbType.DateTime;
                }
                else if (value is double)
                {
                    para.DbType = DbType.Double;
                }
                else if (value is decimal)
                {
                    para.DbType = DbType.Decimal;
                }
                else if (value is Int32)
                {
                    para.DbType = DbType.Int32;
                }
                else if (value is Int16)
                {
                    para.DbType = DbType.Int16;
                }
                else if (value is Int64)
                {
                    para.DbType = DbType.Int64;
                }
                else if (value is Guid)
                {
                    para.DbType = DbType.Guid;
                }
                if (value is byte[])
                {
                    para.DbType = DbType.Binary;
                }
                para.Value = value;
            }
            return para;
        }
        protected DbCommand CreateCommand(string sql, IDictionary paras)
        {
            DbCommand cmd = CreateCommand(sql);
            if (paras != null)
            {
                foreach (var key in paras.Keys)
                {
                    string pname = key.ToString();
                    var value = processParameter(cmd, pname, paras[key]);
                    cmd.Parameters.Add(value);
                }
            }
            return cmd;
        }
        /// <summary>
        /// 创建指令
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras">储存数据的结构体或<see cref="IDictionary<string, object>"/></param>
        /// <returns></returns>
        protected DbCommand CreateCommand(string sql, object paras)
        {
#if DEBUG
            info($"创建指令.[{sql}]");
#endif 
            if (paras == null)
            {
                return CreateCommand(sql);
            }
            else if (paras is IDictionary)
            {
                return CreateCommand(sql, (IDictionary)paras);
            }
            DbCommand cmd = CreateCommand(sql);

            //反射获取参数
            Type t = paras.GetType();
            var pis = t.GetRuntimeProperties();
            foreach (var pi in pis)
            {
                object value = pi.GetValue(paras);
                string pname = pi.Name;
                var para = processParameter(cmd, pname, value);
                if (para != null)
                {
                    cmd.Parameters.Add(para);
                }
            }
            return cmd;
        }

        ///// <summary>
        ///// JSON的JTokenType转为对应的CLI Typpe
        ///// </summary>
        ///// <param name="type"></param>
        ///// <returns></returns>
        //private Type jType2cliType(JTokenType type)
        //{
        //    switch (type)
        //    {
        //        case JTokenType.Array:
        //            return typeof(ArrayList);
        //        case JTokenType.Integer:
        //            return typeof(int);
        //        case JTokenType.Float:
        //            return typeof(float);
        //        case JTokenType.String:
        //            return typeof(string);
        //        case JTokenType.Boolean:
        //            return typeof(bool);
        //        case JTokenType.Date:
        //            return typeof(DateTime);
        //        case JTokenType.Raw:
        //        case JTokenType.Bytes:
        //            return typeof(byte[]);
        //        case JTokenType.Guid:
        //            return typeof(Guid);
        //        case JTokenType.Uri:
        //            return typeof(Uri);
        //        case JTokenType.TimeSpan:
        //            return typeof(TimeSpan);
        //        case JTokenType.Undefined:
        //        case JTokenType.Null:
        //        case JTokenType.None:
        //        case JTokenType.Object:
        //        case JTokenType.Property:
        //        case JTokenType.Comment:
        //        case JTokenType.Constructor:
        //        default:
        //            return typeof(object);
        //    }
        //}

        ///// <summary>
        ///// 创建<see cref="DbCommand"/>
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="paras"></param>
        ///// <returns></returns>
        //protected DbCommand CreateCommand(string sql, JObject paras)
        //{
        //    DbCommand cmd = CreateCommand(sql);
        //    if (paras != null)
        //    {

        //        var pis = paras.Properties();
        //        foreach (var pi in pis)
        //        {
        //            var jvalue = pi.Value;
        //            object value = null;
        //            var type = jType2cliType(jvalue.Type);
        //            if (jvalue != null)
        //            {
        //                value = jvalue.ToObject(type);
        //            }
        //            string pname = pi.Name;
        //            cmd.Parameters.Add(processParameter(cmd, pname, value));
        //        }
        //    }
        //    return cmd;
        //}

        /// <summary>
        /// 开始执行事务,事务提交/回滚后需要释放该对象。
        /// </summary>
        /// <param name="isolationLevel">事务隔离级别，默认不允许脏读。允许脏读的情况请使用<see cref="IsolationLevel.ReadUncommitted"/></param>
        /// <returns></returns>
        public TransactionWarpper BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            if (this.transaction != null)
            {//当前事务未完成
                throw new Exception("在前一个事务释放前不允许开启新的事务。");
            }
#if DEBUG
            info("开始事务.");
#endif
            DbTransaction tran = conn.BeginTransaction(isolationLevel);
            TransactionWarpper warpper = new TransactionWarpper(tran, this.removeCurrentTransaction);
            this.transaction = warpper;
            return warpper;
        }
        /// <summary>
        /// 移除当前事务，在事务处理完毕后调用。
        /// </summary>
        private void removeCurrentTransaction()
        {
            this.transaction?.Dispose();
            this.transaction = null;
        }
        /// <summary>
        /// 确认事务安全，在事务处理完成后调用，确保不存在未处理的数据事务。
        /// </summary>
        public void EnsureTransactionSafe()
        {
            if (this.transaction != null)
            {
                this.transaction.Rollback();
                throw new Exception("数据事务未正确处理，已自动回滚。");
            }
        }


        #endregion

        #region 查询
        /// <summary>
        /// 根据当前数据库类型获取原生的SQL语句
        /// </summary>
        /// <param name="gsql"></param>
        /// <returns></returns>
        public NativeSQLCommandScript GetNativeSQL(GSQLCommandScript gsql)
        {
            return gsql.ToSql(sqlTextGenerator);
        }
        /// <summary>
        /// 使用指定的查询语句获取数据适配器
        /// </summary>
        /// <param name="nsql"></param>
        /// <param name="paras"></param>
        /// <param name="conflictOption">指定将如何检测和解决对数据源的相互冲突的更改。</param>
        /// <returns></returns>
        public DbDataAdapter GetDataAdapter(NativeSQLCommandScript nsql, object paras = null, ConflictOption conflictOption = ConflictOption.OverwriteChanges)
        {
            var cmd = CreateCommand(nsql, paras);
            Open();
            var adapter = this.factory.CreateDataAdapter();
            adapter.SelectCommand = cmd;
            var cmdbuilder = this.factory.CreateCommandBuilder();
            cmdbuilder.DataAdapter = adapter;
            cmdbuilder.ConflictOption = conflictOption;
            cmdbuilder.RefreshSchema();
            adapter.UpdateCommand = cmdbuilder.GetUpdateCommand();
            adapter.InsertCommand = cmdbuilder.GetInsertCommand();
            adapter.DeleteCommand = cmdbuilder.GetDeleteCommand();
            if (this.transaction != null)
            {
                adapter.UpdateCommand.Transaction = this.transaction.Transaction;
                adapter.InsertCommand.Transaction = this.transaction.Transaction;
                adapter.DeleteCommand.Transaction = this.transaction.Transaction;
            }
            return adapter;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns>返回影响的行数</returns>
        public int ExecuteNonQuery(GSQLCommandScript gsql, object paras = null)
        {
            return ExecuteNonQuery(gsql.ToSql(sqlTextGenerator), paras);
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        public int ExecuteNonQuery(NativeSQLCommandScript sql, object paras = null)
        {
            var cmd = CreateCommand(sql, paras);
            Open();
            int result = cmd.ExecuteNonQuery();

            if (CloseWhenExecuted)
            {
                Close();
            }
            return result;
        }

        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(GSQLCommandScript gsql, object paras = null)
        {
            var obj = ExecuteScalar(gsql.ToSql(sqlTextGenerator), paras);
            return SqlUtility.ConvertToTargetType<T>(obj);
        }

        /// <summary>
        /// 查询单值集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T[] ExecuteScalarList<T>(GSQLCommandScript gsql, object paras = null)
        {
            return ExecuteScalarList<T>(gsql.ToSql(sqlTextGenerator), paras);
        }
        /// <summary>
        /// 查询单值集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T[] ExecuteScalarList<T>(NativeSQLCommandScript sql, object paras = null)
        {
            using (DbDataReader reader = ExecuteReader(sql, paras))
            {
                var colcount = reader.FieldCount;
                List<T> l = new List<T>(colcount);
                var type = typeof(T);
                while (reader.Read())
                {
                    object value = reader.GetValue(0);
                    var tobj = SqlUtility.ConvertToTargetType<T>(value);
                    l.Add(tobj);
                }
                reader.Close();
                return l.ToArray();
            }
        }
        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(NativeSQLCommandScript sql, object paras = null)
        {
            var cmd = CreateCommand(sql, paras);
            Open();
            object result = cmd.ExecuteScalar();

            if (CloseWhenExecuted)
            {
                Close();
            }
            return SqlUtility.ConvertToTargetType<T>(result);
        }
        public object ExecuteScalar(GSQLCommandScript gsql, object paras = null)
        {
            return ExecuteScalar(gsql.ToSql(sqlTextGenerator), paras);
        }
        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        // [System.Diagnostics.DebuggerStepThrough]
        public object ExecuteScalar(NativeSQLCommandScript sql, object paras = null)
        {
            var cmd = CreateCommand(sql, paras);
            Open();
            object result = cmd.ExecuteScalar();

            if (CloseWhenExecuted)
            {
                Close();
            }
            return SqlUtility.ConvertToTargetType<object>(result);
        }
        /// <summary>
        /// 查询并返回DataReader
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DbDataReader ExecuteReader(GSQLCommandScript gsql, object paras = null)
        {
            return ExecuteReader(gsql.ToSql(sqlTextGenerator), paras);
        }
        /// <summary>
        /// 查询并返回DataReader
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        [System.Diagnostics.DebuggerStepThrough]
        public DbDataReader ExecuteReader(NativeSQLCommandScript sql, object paras = null)
        {
            var cmd = CreateCommand(sql, paras);
            Open();
            DbDataReader reader = cmd.ExecuteReader();
           
            return reader;

        }
        /// <summary>
        /// 查询32位整数
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int ExecuteInt32(GSQLCommandScript gsql, object paras = null)
        {
            return ExecuteInt32(gsql.ToSql(sqlTextGenerator), paras);
        }
        /// <summary>
        /// 查询32位整数
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int ExecuteInt32(NativeSQLCommandScript sql, object paras = null)
        {
            var obj = ExecuteScalar(sql, paras);
            return Convert.ToInt32(obj);
        }
        /// <summary>
        /// 查询字符串
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public string ExecuteString(GSQLCommandScript gsql, object paras = null)
        {
            return ExecuteString(gsql.ToSql(sqlTextGenerator), paras);
        }
        /// <summary>
        /// 查询字符串
        /// </summary>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public string ExecuteString(NativeSQLCommandScript sql, object paras = null)
        {
            var obj = ExecuteScalar(sql, paras);
            if (obj == null)
            {
                return null;
            }
            return Convert.ToString(obj);
        }
        /// <summary>
        /// 将查询结果写入指定的模型类数组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T[] ExecuteArray<T>(GSQLCommandScript gsql, object paras = null) where T : new()
        {
            return ExecuteArray<T>(gsql.ToSql(sqlTextGenerator), paras);
        }

        /// <summary>
        /// 将查询结果写入指定的模型类数组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T[] ExecuteArray<T>(NativeSQLCommandScript sql, object paras = null) where T : new()
        {
            using (DbDataReader reader = ExecuteReader(sql, paras))
            {
                List<T> list = new List<T>();
                while (reader.Read())
                {
                    var tobj = DbReader2Object<T>(reader);
                    list.Add(tobj);
                }
                reader.Close();
                return list.ToArray();
            }
        }

        /// <summary>
        /// 查询一行数据，并将数据写入指定的模型类中返回。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T ExecuteObject<T>(GSQLCommandScript gsql, object paras = null) where T : new()
        {
            return ExecuteObject<T>(gsql.ToSql(sqlTextGenerator), paras);

        }
        /// <summary>
        /// 查询一行数据，并将数据写入指定的模型类中返回。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="gsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public T ExecuteObject<T>(NativeSQLCommandScript nsql, object paras = null) where T : new()
        {
            using (DbDataReader reader = ExecuteReader(nsql, paras))
            {
                var colcount = reader.FieldCount;
                var type = typeof(T);
                var havedata = reader.Read();
                T data;
                if (havedata)
                {
                    data = DbReader2Object<T>(reader);
                }
                else
                {
                    data = default(T);
                }
                reader.Close();
                return data;
            }
        }

        /// <summary>
        /// 获取类型中的属性，忽略属性名大小写
        /// </summary>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <param name="bindingFlags"></param>
        /// <returns></returns>
        private static PropertyInfo GetPropertyIgnoreCase(Type type, string name, BindingFlags bindingFlags)
        {
            var ps = type.GetProperties();
            foreach (var item in ps)
            {
                if (string.Compare(item.Name, name, true) == 0)
                {
                    return item;
                }
            }
            return null;
        }


        /// <summary>
        /// 从<see cref="DbDataReader"/>读取对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader"></param>
        /// <returns></returns>
        public static T DbReader2Object<T>(DbDataReader reader) where T : new()
        {
            var type = typeof(T);
            var colcount = reader.FieldCount;
            T instance = Activator.CreateInstance<T>();
            object obj = (object)instance;//装箱操作，很必要，不然对于值类型的反射赋值操作会无效。
            for (int i = 0; i < colcount; i++)
            {
                object value = reader.GetValue(i);
                string name = reader.GetName(i);

                var prop = type.GetProperty(name, BindingFlags.Public | BindingFlags.Instance);

                if (prop == null)
                {//获取不到属性，尝试通过忽略属性名称大小写来查找
                    prop = GetPropertyIgnoreCase(type, name, BindingFlags.Public | BindingFlags.Instance);
                }

                if (prop != null)
                {
                    if (prop.SetMethod == null)
                    {//忽略不可写的属性
                        continue;
                    }
                    var cval = SqlUtility.ConvertToTargetType(value, prop.PropertyType);
                    prop.SetValue(obj, cval);
                }
                else
                {
                    var field = type.GetField(name, BindingFlags.Public | BindingFlags.Instance);
                    if (field != null)
                    {
                        if (field.IsInitOnly)
                        {//忽略只读字段
                            continue;
                        }
                        var cval = SqlUtility.ConvertToTargetType(value, field.FieldType);
                        field.SetValue(obj, cval);
                    }

                }
            }

            var tobj = SqlUtility.ConvertToTargetType<T>(obj);
            return tobj;
        }

        #region ADO.NET
        /// <summary>
        /// 查询一行数据
        /// </summary>
        /// <param name="nsql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataRow ExecuteDataRow(NativeSQLCommandScript nsql, object paras = null)
        {
            DataTable tb = ExecuteDataTable(nsql, null, paras);
            if (tb.Rows.Count > 0)
            {
                return tb.Rows[0];
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 查询一个数据表
        /// </summary>
        /// <param name="nsql"></param>
        /// <param name="table">传入非null<see cref="DataTable"/>则直接填充该<see cref="DataTable"/>,传入null则生成新的<see cref="DataTable"/>。</param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(NativeSQLCommandScript nsql, DataTable table = null, object paras = null)
        {
            var cmd = CreateCommand(nsql, paras);
            Open();
            if (table == null)
            {
                table = new DataTable();
            }
            if (!this.factory.CanCreateDataAdapter)
            {
                throw new Exception("当前数据工厂不允许创建数据适配器。");
            }
            using (var adapter = this.factory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(table);
            }
            if (CloseWhenExecuted)
            {
                Close();
            }

            return table;
        }
        public DataSet ExecuteDataSet(NativeSQLCommandScript nsql, object paras = null)
        {
            var cmd = CreateCommand(nsql, paras);
            Open();
            var ds = new DataSet();
            using (var adapter = this.factory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
            }
            if (CloseWhenExecuted)
            {
                Close();
            }
            return ds;
        }
        #endregion


        ///// <summary>
        ///// 查询一行数据，将查询结果作为<see cref="JObject"/>返回
        ///// </summary>
        ///// <param name="gsql"></param>
        ///// <param name="paras"></param>
        ///// <returns></returns>
        //public JObject ExecuteJObject(GSQLCommandScript gsql, object paras = null)
        //{
        //    return ExecuteJObject(gsql.ToSql(sqlTextGenerator), paras);
        //}
        ///// <summary>
        ///// 执行SQL语句，将查询结果作为JSON对象返回。
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="paras"></param>
        ///// <returns></returns>
        //public JsonElement ExecuteJObject(NativeSQLCommandScript sql, object paras = null)
        //{
        //    ArrayBufferWriter<byte> Jsonbyte = new ArrayBufferWriter<byte>();
        //    using (var jsonWriter = new Utf8JsonWriter(Jsonbyte))
        //    {
        //        jsonWriter.WriteStartObject();
        //        using (DbDataReader reader = ExecuteReader(sql, paras))
        //        {
        //            var colcount = reader.FieldCount;
        //            while (reader.Read())
        //            { 
        //                for (int i = 0; i < colcount; i++)
        //                {
        //                    string colname = reader.GetName(i);
        //                    object value = reader.GetValue(i);
        //                    value = SqlUtility.ConvertToTargetType<object>(value);
        //                    //if (obj.Property(colname) != null)
        //                    //{//属性名重复
        //                    //    continue;
        //                    //}
        //                    JsonProperty property = new JsonProperty(); 
        //                    jsonWriter.wri
        //                    obj.Add(colname, new JValue(value));
        //                }
        //                return obj;
        //            }
        //            return null;
        //        }
        //    } 
        //}
        #endregion

        #region 查询扩展
        /// <summary>
        /// 使用GSQL语法进行分页查询
        /// </summary>
        /// <typeparam name="TModel">单行数据的模型</typeparam>
        /// <param name="select">查询所有数据的select语句</param>
        /// <param name="onepagerowscount">一页数据的数量</param>
        /// <param name="curpageindex">要查询数据的页索引（页索引从0开始）</param>
        /// <param name="paras">查询参数</param>
        /// <returns></returns>
        public virtual SqlPagination<TModel> ExecutePagination<TModel>(GSQLCommandScript select, int onepagerowscount, int curpageindex, object paras = null) where TModel : new()
        {
            if (onepagerowscount < 1)
            {
                throw new Exception("分页查询时，一页的数量不能小于1.");
            }
            if (curpageindex < 0)
            {
                throw new Exception("分页查询时当前页索引不能小于0.");
            }
            var sqlcount = $"SELECT COUNT(*) FROM ({select.CommandText}) AS table1;";
            var allcount = this.ExecuteInt32(sqlcount, paras);
            var pagecount = (int)Math.Ceiling((decimal)allcount / (decimal)onepagerowscount);
            int limitstart = curpageindex * onepagerowscount;

            string query = $"SELECT * FROM ({select.CommandText}) AS table2 LIMIT {limitstart},{onepagerowscount}";
            var data = this.ExecuteArray<TModel>(query, paras);
            SqlPagination<TModel> pagi = new SqlPagination<TModel>() { CurrentPage = curpageindex, PageCount = pagecount, AllDataCount = allcount };
            pagi.PageData = data;
            return pagi;
        }
        /// <summary>
        /// 基于原生语句的分页查询需要在各自的数据库类中实现
        /// </summary>
        /// <typeparam name="TModel">单行数据的模型</typeparam>
        /// <param name="select">查询所有数据的select语句</param>
        /// <param name="onepagerowscount">一页数据的数量</param>
        /// <param name="curpageindex">要查询数据的页索引（页索引从0开始）</param>
        /// <param name="paras">查询参数</param>
        /// <returns></returns>
        public abstract SqlPagination<TModel> ExecutePagination<TModel>(NativeSQLCommandScript select, int onepagerowscount, int curpageindex, object paras = null) where TModel : new();

        #endregion

        #region Helper

        /// <summary>
        /// 创建一个原生SQL语句生成器
        /// </summary>
        /// <returns></returns>
        public SQLBuilder CreateNativeSQLBuilder()
        {
            return new SQLBuilder(this.sqlTextGenerator);
        }
        /// <summary>
        /// 创建一个通用GSQL语句生成器
        /// </summary>
        /// <returns></returns>
        public SQLBuilder CreateGeneralSQLBuilder()
        {
            return new SQLBuilder(new GSQLTextGenerator());
        }

        /// <summary>
        /// 创建指定DAO类关联的数据结构
        /// </summary>
        /// <typeparam name="TDAO"></typeparam>
        /// <param name="database"></param>
        public void CreateDAOSchema<TDAO>() where TDAO : DAOS.DAO
        {
            var t = typeof(TDAO);
            this.CreateDAOSchema(t);
        }
        /// <summary>
        /// 创建指定DAO类关联的数据结构
        /// </summary>
        /// <typeparam name="TDAL"></typeparam>
        /// <param name="database"></param>
        public void CreateDAOSchema(Type daotype)
        {
            var dal = (DAOS.DAO)Activator.CreateInstance(daotype, this, -1);
            if (dal == null)
            {
                throw new Exception($"无法创建DAO的实例。类型：[{daotype}]");
            }
            dal?.InitSchema();
        }

        #endregion

        private static object consoleLocker = new object();
        private void info(string message)
        {
#if DEBUG

            lock (consoleLocker)
            {
                Console.ForegroundColor = ConsoleColor.Green;
                Console.Write("数据库操作");
                Console.ForegroundColor = ConsoleColor.Gray;
                Console.Write($"[{this.GetHashCode()}]");
                Console.ForegroundColor = ConsoleColor.Cyan;
                Console.Write(":");
                Console.WriteLine(message);
            }
#endif
        }


        public virtual Database Clone()
        {
            var newdb = Database.CreateInstance(this.GetType(), this.connstr);
            newdb.CloseWhenExecuted = this.CloseWhenExecuted;
            return newdb;
        }

        public bool IsDisposed { get; private set; } = false;
        public virtual void Dispose()
        {
            if (!IsDisposed)
            {
#if DEBUG
                info("释放数据连接实例。");
#endif
                IsDisposed = true;
                this.Close();
                this.conn?.Dispose();
            }

        }

        ~Database()
        {
            this.Dispose();
        }
    }
}
